--    Program: 		CAPAREXT_INVOICE.sql
--    Description:  Extract AR Invoice information 
--    Revision:     1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   l_set_of_books_id		number;
                    		  
   cursor cur_invoice (p_set_of_books_id number) is
      select * from RA_CUSTOMER_TRX_ALL 
      where set_of_books_id = p_set_of_books_id;

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_INVOICE.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
       --raise;
   end;      

   for cur in cur_invoice(l_set_of_books_id) loop   	
     
     -- output data 
     utl_file.put_line( l_utl_file, 
       cur.CUSTOMER_TRX_ID                 || chr(9) ||
       cur.LAST_UPDATE_DATE                || chr(9) ||
       cur.LAST_UPDATED_BY                 || chr(9) ||
       cur.CREATION_DATE                   || chr(9) ||
       cur.CREATED_BY                      || chr(9) ||
       cur.LAST_UPDATE_LOGIN               || chr(9) ||
       cur.TRX_NUMBER                      || chr(9) ||
       cur.CUST_TRX_TYPE_ID                || chr(9) ||
       cur.TRX_DATE                        || chr(9) ||
       cur.SET_OF_BOOKS_ID                 || chr(9) ||
       cur.BILL_TO_CONTACT_ID              || chr(9) ||
       cur.BATCH_ID                        || chr(9) ||
       cur.BATCH_SOURCE_ID                 || chr(9) ||
       cur.REASON_CODE                     || chr(9) ||
       cur.SOLD_TO_CUSTOMER_ID             || chr(9) ||
       cur.SOLD_TO_CONTACT_ID              || chr(9) ||
       cur.SOLD_TO_SITE_USE_ID             || chr(9) ||
       cur.BILL_TO_CUSTOMER_ID             || chr(9) ||
       cur.BILL_TO_SITE_USE_ID             || chr(9) ||
       cur.SHIP_TO_CUSTOMER_ID             || chr(9) ||
       cur.SHIP_TO_CONTACT_ID              || chr(9) ||
       cur.SHIP_TO_SITE_USE_ID             || chr(9) ||
       cur.SHIPMENT_ID                     || chr(9) ||
       cur.REMIT_TO_ADDRESS_ID             || chr(9) ||
       cur.TERM_ID                         || chr(9) ||
       cur.TERM_DUE_DATE                   || chr(9) ||
       cur.PREVIOUS_CUSTOMER_TRX_ID        || chr(9) ||
       cur.PRIMARY_SALESREP_ID             || chr(9) ||
       cur.PRINTING_ORIGINAL_DATE          || chr(9) ||
       cur.PRINTING_LAST_PRINTED           || chr(9) ||
       cur.PRINTING_OPTION                 || chr(9) ||
       cur.PRINTING_COUNT                  || chr(9) ||
       cur.PRINTING_PENDING                || chr(9) ||
       cur.PURCHASE_ORDER                  || chr(9) ||
       cur.PURCHASE_ORDER_REVISION         || chr(9) ||
       cur.PURCHASE_ORDER_DATE             || chr(9) ||
       cur.CUSTOMER_REFERENCE              || chr(9) ||
       cur.CUSTOMER_REFERENCE_DATE         || chr(9) ||
       cur.COMMENTS                        || chr(9) ||
       cur.INTERNAL_NOTES                  || chr(9) ||
       cur.EXCHANGE_RATE_TYPE              || chr(9) ||
       cur.EXCHANGE_DATE                   || chr(9) ||
       cur.EXCHANGE_RATE                   || chr(9) ||
       cur.TERRITORY_ID                    || chr(9) ||
       cur.INVOICE_CURRENCY_CODE           || chr(9) ||
       cur.INITIAL_CUSTOMER_TRX_ID         || chr(9) ||
       cur.AGREEMENT_ID                    || chr(9) ||
       cur.END_DATE_COMMITMENT             || chr(9) ||
       cur.START_DATE_COMMITMENT           || chr(9) ||
       cur.LAST_PRINTED_SEQUENCE_NUM       || chr(9) ||
       cur.ATTRIBUTE_CATEGORY              || chr(9) ||
       cur.ATTRIBUTE1                      || chr(9) ||
       cur.ATTRIBUTE2                      || chr(9) ||
       cur.ATTRIBUTE3                      || chr(9) ||
       cur.ATTRIBUTE4                      || chr(9) ||
       cur.ATTRIBUTE5                      || chr(9) ||
       cur.ATTRIBUTE6                      || chr(9) ||
       cur.ATTRIBUTE7                      || chr(9) ||
       cur.ATTRIBUTE8                      || chr(9) ||
       cur.ATTRIBUTE9                      || chr(9) ||
       cur.ATTRIBUTE10                     || chr(9) ||
       replace(cur.ORIG_SYSTEM_BATCH_NAME,chr(9),'^C9^')          || chr(9) ||
       cur.POST_REQUEST_ID                 || chr(9) ||
       cur.REQUEST_ID                      || chr(9) ||
       cur.PROGRAM_APPLICATION_ID          || chr(9) ||
       cur.PROGRAM_ID                      || chr(9) ||
       cur.PROGRAM_UPDATE_DATE             || chr(9) ||
       cur.FINANCE_CHARGES                 || chr(9) ||
       cur.COMPLETE_FLAG                   || chr(9) ||
       cur.POSTING_CONTROL_ID              || chr(9) ||
       cur.BILL_TO_ADDRESS_ID              || chr(9) ||
       cur.RA_POST_LOOP_NUMBER             || chr(9) ||
       cur.SHIP_TO_ADDRESS_ID              || chr(9) ||
       cur.CREDIT_METHOD_FOR_RULES         || chr(9) ||
       cur.CREDIT_METHOD_FOR_INSTALLMENTS  || chr(9) ||
       cur.RECEIPT_METHOD_ID               || chr(9) ||
       cur.ATTRIBUTE11                     || chr(9) ||
       cur.ATTRIBUTE12                     || chr(9) ||
       cur.ATTRIBUTE13                     || chr(9) ||
       cur.ATTRIBUTE14                     || chr(9) ||
       cur.ATTRIBUTE15                     || chr(9) ||
       cur.RELATED_CUSTOMER_TRX_ID         || chr(9) ||
       cur.INVOICING_RULE_ID               || chr(9) ||
       cur.SHIP_VIA                        || chr(9) ||
       cur.SHIP_DATE_ACTUAL                || chr(9) ||
       cur.WAYBILL_NUMBER                  || chr(9) ||
       cur.FOB_POINT                       || chr(9) ||
       cur.CUSTOMER_BANK_ACCOUNT_ID        || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE1     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE2     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE3     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE4     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE5     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE6     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE7     || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE8     || chr(9) ||
       cur.INTERFACE_HEADER_CONTEXT        || chr(9) ||
       cur.DEFAULT_USSGL_TRX_CODE_CONTEXT  || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE10    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE11    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE12    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE13    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE14    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE15    || chr(9) ||
       cur.INTERFACE_HEADER_ATTRIBUTE9     || chr(9) ||
       cur.DEFAULT_USSGL_TRANSACTION_CODE  || chr(9) ||
       cur.RECURRED_FROM_TRX_NUMBER        || chr(9) ||
       cur.STATUS_TRX                      || chr(9) ||
       cur.DOC_SEQUENCE_ID                 || chr(9) ||
       cur.DOC_SEQUENCE_VALUE              || chr(9) ||
       cur.PAYING_CUSTOMER_ID              || chr(9) ||
       cur.PAYING_SITE_USE_ID              || chr(9) ||
       cur.RELATED_BATCH_SOURCE_ID         || chr(9) ||
       cur.DEFAULT_TAX_EXEMPT_FLAG         || chr(9) ||
       cur.CREATED_FROM                    || chr(9) ||
       cur.ORG_ID                          || chr(9) ||
       cur.WH_UPDATE_DATE                  || chr(9) ||
       cur.GLOBAL_ATTRIBUTE1               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE2               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE3               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE4               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE5               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE6               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE7               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE8               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE9               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE10              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE11              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE12              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE13              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE14              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE15              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE16              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE17              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE18              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE19              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE20              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE21              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE22              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE23              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE24              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE25              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE26              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE27              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE28              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE29              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE30              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
       cur.EDI_PROCESSED_FLAG              || chr(9) ||
       cur.EDI_PROCESSED_STATUS            || chr(9) ||
       cur.MRC_EXCHANGE_RATE_TYPE          || chr(9) ||
       cur.MRC_EXCHANGE_DATE               || chr(9) ||
       cur.MRC_EXCHANGE_RATE               || chr(9) ||
       cur.PAYMENT_SERVER_ORDER_NUM        || chr(9) ||
       cur.APPROVAL_CODE                   || chr(9) ||
       cur.ADDRESS_VERIFICATION_CODE       || chr(9) ||
       cur.OLD_TRX_NUMBER                  || chr(9) ||
       cur.BR_AMOUNT                       || chr(9) ||
       cur.BR_UNPAID_FLAG                  || chr(9) ||
       cur.BR_ON_HOLD_FLAG                 || chr(9) ||
       cur.DRAWEE_ID                       || chr(9) ||
       cur.DRAWEE_CONTACT_ID               || chr(9) ||
       cur.DRAWEE_SITE_USE_ID              || chr(9) ||
       cur.REMITTANCE_BANK_ACCOUNT_ID      || chr(9) ||
       cur.OVERRIDE_REMIT_ACCOUNT_FLAG     || chr(9) ||
       cur.DRAWEE_BANK_ACCOUNT_ID          || chr(9) ||
       cur.SPECIAL_INSTRUCTIONS            || chr(9) ||
       cur.REMITTANCE_BATCH_ID             || chr(9) ||
       cur.PREPAYMENT_FLAG                 || chr(9) ||
       cur.CT_REFERENCE                    || chr(9) ||
       cur.CONTRACT_ID                     || chr(9) ||
       cur.BILL_TEMPLATE_ID   
	 );                        
                               
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': AR invoice not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_INVOICE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

--exit
